1. Field of the Invention
The present invention relates to a method and system for graphically representing and merging Boolean logic and mathematical relationship operators. More specifically, for generating search queries.
2. Description of the Prior Art
Whether on paper, CD-ROM, or the Internet, information has been organized into collections known as databases. On paper these databases, though organized into collections, are flowing text and therefore non-structured. In electronic media databases are either structured, non-structured, or hybrid form, where the hybrid form is a database having both structured and non-structured components.
Structured databases are organized into records, each record having discrete fields of information, e.g., an employee data record containing fields such as employee name, address, and salary. In a non-structured database, each item may correspond to an article or legal opinion, which is in free form text. Hybrid format databases include records that contain free form text as well as explicit fields. For example, a database designed to store journal information would include fields such as the date of the article, the name of the authors, and the free flowing article text stored in a memo field.
With the advent of collections of paper based non-structured data, tools were developed to find information because it was too hard to search for something by scanning the information by eye. Similarly, in an electronic collection, it takes too long to scan the information from the beginning to the end of the database. The table of contents and index within books are familiar tools for quickly locating information. The Dewey Decimal System is also a familiar tool for locating information across vast collections of paper within libraries. With the advent of electronic information, it was natural to extend the concepts of a table of contents to electronic outlines or trees with links for non-structured databases or a xe2x80x9cTOCxe2x80x9d field within a structured database. A book""s index was extended to xe2x80x98indexingxe2x80x99 the words of both structured and non-structured databases wherein a table of words was created electronically by scanning the words in the collection. Within this table was the location of the word. In other words, it contained not only the word found but, within what electronic document and the count of where it was with respect to the beginning of the document. Similarly, xe2x80x9cindexingxe2x80x9d words contained within the fields of structured databases lead to high speed searches of information because the index contained the record number where the word was found. Of course as is the case with a paper collection of information, scanning the information can be electronically modeled by bruit force xe2x80x9cmatchingxe2x80x9d searches of databases without the use of an index.
The index of a book however was not limited to single word locations but combinations of words that constituted a concept. For example, a book on birds could contain an index entry on the xe2x80x9cLong Necked Geesexe2x80x9d and xe2x80x9cCanadian Geesexe2x80x9d. With a simple single word electronic search, searching for xe2x80x9cGeesexe2x80x9d alone would yield both entries when the user desires only one.
With structured databases the need existed to retrieve records based on counts and amounts. For example, the searcher would desire to see all patrons who owed more then $10.00 and items that had been purchased in quantities less than or equal to 10.
As a result, electronic search engines borrowed from mathematics and Boolean logic to yield syntactical representations for complex searches. The original syntax included left and right parenthesis, and, or, not, quotations, greater than, less than, equals and not equals. In addition, the arrangements of these equational elements dictate the meanings and consequently effect the results. For example, (xe2x80x9cLongxe2x80x9d AND xe2x80x9cNeckedxe2x80x9d AND xe2x80x9cGeesexe2x80x9d) satisfied the non-structured example above while (OwedField greater than $10.00 or QuantityField less than =10) satisfies the structured database example. Please note that the xe2x80x9cORxe2x80x9d in this equational expression is opposite of the xe2x80x9cANDxe2x80x9d used in the sentence. This syntax is too complicated for the general untrained user. Even the proper placement of parenthesis had significant impact on the result. For example, (xe2x80x9cDogxe2x80x9d AND xe2x80x9cCatxe2x80x9d) OR xe2x80x9cMousexe2x80x9d had a much different result than xe2x80x9cDogxe2x80x9d AND (xe2x80x9cCatxe2x80x9d OR xe2x80x9cMousexe2x80x9d). Just the concept of xe2x80x98ORxe2x80x99 vs. xe2x80x98ANDxe2x80x99 is complex for the general software user to understand. The widespread use of computers to access information by all levels of users and the sheer volume of information that is accessible has placed the burden of simplicity on the technology instead of on the level of sophistication of the user.
In an effort to minimize the complexity in forming particularized queries, different syntactical representations of queries have been developed. The Structured Query Language (SQL) was developed as a standard syntax for searches, for example. The theory is that standardization limits what the user has to learn as they move from software application to application. In addition, it moved the syntax closer to sentence structure and hid the use of indexes from the user. For example, xe2x80x9cSelect LastName from Clients Where (OwedField greater than $10.00 OR QuantityField less than =10);xe2x80x9d performs the same search (i.e. query) as the above example. Please note that though the syntax is different the Boolean and mathematical elements in the expression section of the SQL statement are still present. Obviously, even in this simplest of expressions, it is well beyond the masses.
As a consequence query expression builders have been developed. These expression builders act as a user interface that generates the Boolean and mathematical expressions that in turn defines the search. One example of an expression builder is a natural language query engine, U.S. Pat. No. 5,175,814 to Anick et al, which is incorporated by reference for its teachings. To generate a natural language query, a user forms a basic sentence that describes what they are looking for. A natural language query of the prior example would be something like, xe2x80x9cShow me all of the client last names where what they owe is greater than $10 and the quantity of items purchased is less than or equal to 10.xe2x80x9d This sentence is then translated into a mathematical syntax query, SQL Syntax, or any other search/query syntax. The resultant syntactical output from natural language query generator is then used to locate records in the database meeting the desired criteria. The user may not even see the actual Boolean and evaluation query expression that is used to search the database or, if shown, understand it. As a consequence, the sentence they type for the natural language query may be returning results that the user did not intend to find. The user has limited mechanisms to corroborate that the sentence they typed is providing an expression syntax that matches. This sentence based representation, though rich in xe2x80x9cgrammarxe2x80x9d lacks underlying structure that adds meaning to the interpretation.
To resolve this problem Anick et al supplied a secondary xe2x80x9ctilexe2x80x9d based interface between the natural language query and the search expression syntax. At the user""s request, xe2x80x9ctilesxe2x80x9d are presented to the user representing each of the Boolean terms in the expression query syntax generated from the natural language query. The user may then manipulate the tiles to modify their initial Natural Language query and in turn the output query expression. While graphic in nature, it is a xe2x80x9cflow chartingxe2x80x9d approach to representing the logic as is evidenced by the need to include logical relationships such as greater than, less than, equal, not equal, and partial matches in text form within the xe2x80x9ctilexe2x80x9d or flow chart block. This flow diagram representation, though possessing xe2x80x9cstructurexe2x80x9d, lacks underlying xe2x80x9cgrammarxe2x80x9d that adds xe2x80x9cmeaningxe2x80x9d to the interpretation. It provides little or no intuitive aid to understanding the underlying Boolean or evaluative relationships between the items used to formulate the search query.
Strong structure and lack of grammar is again evidenced by similar xe2x80x9cflow chartingxe2x80x9d approaches that have been taken in U.S. Pat. No. 5,428,776 to Rothfield and U.S. Pat. No. 5,721,900 to Banning et al, which are incorporated by reference for their teachings. Rothfield applies this approach specifically to SQL syntax generation while Banning et al applies it to expression syntax.
In an attempt at adding grammar to flow charting U.S. Pat. No. 5,701,456 to Jacopi et al, which are incorporated by reference for their teachings, uses the lines of the flow chart arranged in series or parallel as the Boolean components of the expression. This arrangement closely parallels circuit diagrams used in electronics with series and parallel resistors and capacitors. The approach falls short because the complexity of the xe2x80x9cgrammarxe2x80x9d is directly related to the complexity of the query. In other words, the xe2x80x9cgrammarxe2x80x9d is limited due to the complexity of the query, and the added grammar becomes even more complex as the query expression gets complex. In fact, this approach produces expressions that are more difficult to understand than their mathematical or SQL counterparts.
In addition to the above discussion of the xe2x80x9cstructurexe2x80x9d and xe2x80x9cgrammarxe2x80x9d elements in current art, none of the above approaches are purely graphic in nature with respect to logical and mathematical expressions such a  greater than ,  less than ,  less than  greater than , =, etc. Though flow diagrams are using graphic elements, the representations still contain characters that are evaluative in nature.
U.S. Pat. No. 5,592,663 to Nagamori, incorporated by reference teaches a truly graphic mechanism for representing the results of a query in terms of sets. This approach is rich in graphic xe2x80x9cgrammarxe2x80x9d and xe2x80x9cstructurexe2x80x9d. The joining of sets of query results in overlays of graphic regions. Overlapping regions represent where the sets of records have common results. Where they do not overlap shows where the record sets are distinct. This is equivalent to the Boolean xe2x80x9candxe2x80x9d and xe2x80x9corxe2x80x9d operators between record sets. This is the xe2x80x9cgrammarxe2x80x9d. Once the rectangular regions are defined they may be manipulated to alter the parameters associated with creating the set of records represented by the region. This is the xe2x80x9cstructurexe2x80x9d. The intuitive nature of viewing sets of records graphically is outstanding in this approach. However, generating the different record sets that are represented by the rectangles is still a standard equational element syntax such as (OwedField greater than $10.00 or QuantityField less than =10). This approach is very useful in graphically representing and analyzing the relationships between two or more sets of records that are generated by query expressions. In addition, though this approach contains xe2x80x9cgrammarxe2x80x9d and xe2x80x9cstructurexe2x80x9d it lacks the xe2x80x9cmeaningxe2x80x9d to produce a graphic xe2x80x9clanguagexe2x80x9d.
There is a need for an arrangement that enables search queries to be generated without the necessity of a programming language knowledge.
There is also a need for an arrangement that enables intuitive graphic-based objects to be translated into executable queries for a database.
These and other needs are attained by the present invention, where a graphical query generation arrangement receives graphical objects, generated by a graphic user interface, and generates a database search query according to a prescribed syntax based on the query elements.
According to one aspect of the present invention, a method is provided of graphically generating a query expression for use by a database system. The method includes the steps of first generating graphical objects, representing prescribed elements according to a prescribed syntax, in response to user inputs to a graphic user interface, and second generating the query elements from the graphical objects based on a correlation thereof with the prescribed syntax. Generation of the graphical object provides visual feedback to a user of a mouse, keyboard or some other graphical input tool, providing easy input of graphical objects. Moreover, the generation of the query elements based on a correlation of the graphical objects and the prescribed syntax enables users to generate query expressions using graphical paradigms, eliminating the necessity for programming knowledge or advanced search skills. Rather, an intuitive, graphic-based input system may be used, enabling users to generate relatively complex query expressions for database systems with little or no programming or database experience.
Another aspect of the present invention provides an apparatus for generating a search query for a database of records. The apparatus includes a graphic user interface configured for generating graphical objects in response to user inputs, and a graphic query generator. The graphic query generator is configured generating the search query according to a prescribed syntax based on the graphical objects.
Additional advantages and novel features of the invention will be set forth in part in the description which follows, and in part will become apparent to those skilled in the art upon examination of the following or may be learned by practice of the invention. The advantages of the invention may be realized and attained by means of the instrumentalities and combinations particularly pointed out in the appended claims.